package net.zjitc.dao.impl;

import net.zjitc.dao.ProductDao;
import net.zjitc.pojo.Evaluate;
import net.zjitc.pojo.Product;
import net.zjitc.pojo.User;
import net.zjitc.utils.PageBean;
import net.zjitc.utils.db.C3P0Utils;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2017/11/22.
 */
public class ProductDaoImpl implements ProductDao {
    public PageBean<Product> findPageBean(int page, int pageSize) {
        //pageBean的参数都是什么时候设置进去的，一定要设置，要不页面取不到
        PageBean<Product> pageBean = new PageBean<Product>();
        pageBean.setPage(page);
        pageBean.setPageSize(pageSize);
        //查总条数
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select count(*) from product";
        //要符合条件的
        String ssql = "select * from product order by pid limit ?,?";
        try {
            Number num = (Number) runner.query(sql,new ScalarHandler());
            int count = num.intValue();
            pageBean.setCount(count);
            List<Product>  list = (List<Product>) runner.query(ssql,new BeanListHandler(Product.class),(page-1)*pageSize,pageSize);
            pageBean.setPageList(list);
            return pageBean;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public int insert(Product product) {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "insert into product(pname,market_price,shop_price,image,pdesc,is_hot,pdate,csid) value(?,?,?,?,?,?,?,?)";
        try {
            int count = runner.update(sql,product.getPname(),product.getMarket_price(),product.getShop_price(),
                    product.getImage(),product.getPdesc(),product.getIs_hot(),product.getPdate(),product.getCategorySecond().getCsid());
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public PageBean<Product> findPageBeanByCid(int page, int pageSize, int cid) {
        PageBean<Product> bean = new PageBean<Product>();
        bean.setPage(page);
        bean.setPageSize(pageSize);
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String  sqlCount = "SELECT COUNT(*) FROM product p JOIN categorysecond cs, category c WHERE p.csid = cs.csid AND cs.cid = c.cid AND c.cid = ?";
        String sql = "SELECT p.* FROM product p JOIN categorysecond cs, category c WHERE p.csid = cs.csid AND cs.cid = c.cid AND c.cid = ? limit ?,?";

        try {
            //查询有多少条数据
            Number num = (Number) runner.query(sqlCount,new ScalarHandler(),cid);
            bean.setCount(num.intValue());

            //查数据
            List<Product>  list = runner.query(sql,new BeanListHandler<Product>(Product.class),cid,(page-1)*pageSize,pageSize);
            bean.setPageList(list);
            return bean;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public Product findByPid(int pid) {
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "select * from product where pid = ?";
        try {
            Product product = runner.query(sql,new BeanHandler<Product>(Product.class),pid);
            return product;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public List<Evaluate> findEvaluateByPid(int pid){
        QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
        String sql = "SELECT * FROM evaluate e JOIN product p,USER u WHERE e.pid = p.pid AND e.uid = u.uid AND p.pid = ?";
        /**
         * 思考：通过此语句查出来的记录，包含了三个表所有字段的记录（三个bean模式字段），只需要将记录里面对应的值放到相应的bean对象中就可以了
         * 问题1？？三个字段无法通过new BeanHand()来做，（只适合一个bean模式的）
         * 解决方式：将一条记录中的所有的字段封装成一个map集合，形成key-->value 关系，其中，key表示的是字段名称，value表示字段对应的值
         *           创建三个bean模型：product user evaluate
         *           使用BeanUtils(user,m) 直接赋值，因为m里面包含三个对象的值，此处只会从中找能对应起来的字段赋值进去
         *  问题2：上面解决的是一条记录的情况，有可能查出多条记录，
         *  解决方式：1.使用一个集合list,里面存放多个map对象
         *            2.每条记录的取出，只需要使用循环取出便可
         */
        //存入评价对象的集合
        List<Evaluate> evaluateList = new ArrayList<Evaluate>();
        //存入map对象的list集合
        List<Map<String,Object>> list = new ArrayList<Map<String, Object>>();
        try {
            list = runner.query(sql,new MapListHandler(),pid);
            //每次从list中取出map对象，然后封装成三个实体类对象
            for (Map<String,Object> m : list){
                User user = new User();
                Product product = new Product();
                Evaluate evaluate = new Evaluate();
                //使用BeanUtils封装对象
                BeanUtils.populate(user,m);
                BeanUtils.populate(product,m);
                BeanUtils.populate(evaluate,m);
                //用户user及产品product为评价对象的成员，需要设置进去
                evaluate.setUser(user);
                evaluate.setProduct(product);
                //每个评价对象，添加到评价集合中
                evaluateList.add(evaluate);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

            return evaluateList;
    }

    public static void main(String[] args) {
        ProductDaoImpl dao = new ProductDaoImpl();
        List<Evaluate> list = dao.findEvaluateByPid(157);
        System.out.println(list);
    }
}
